package com.travel.dao.impl;

/**
 * 评论功能
 * @author zourenhe
 *
 */

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.travel.dao.ScenicCommentDao;
import com.travel.entity.Comment;
import com.travel.entity.Hotel;
import com.travel.entity.Scenic;
import com.travel.entity.ScenicComment;
import com.travel.entity.User;
import com.travel.util.DBUtils;
import com.travel.util.PageInfo;

public class ScenicCommentDaoImpl implements ScenicCommentDao{

	private  DBUtils  db= new DBUtils();
	
	@Override
	public List<ScenicComment> find() {
		List<ScenicComment>  list= new ArrayList<ScenicComment>();
		StringBuffer sb=new StringBuffer();
		sb.append("select")
		.append(" C.comment_id,")
		.append(" C.comment_content,")
		.append(" C.comment_datetime,")
		.append(" C.user_id,")
		.append(" C.scenic_id,")
		.append(" C.parent_comment_id,")
		.append(" U.user_name,")
		.append(" U.user_picture,")
		.append(" S.scenic_name")
		.append(" from tab_scenic_comment C")
		.append(" inner join tab_user U")
		.append("  on C.user_id=U.user_id")
		.append(" inner join tab_scenic S")
		.append("  on C.scenic_id=S.scenic_id");
//		ResultSet rSet=db.query("select C.comment_id,C.comment_content,C.comment_datetime,C.user_id,C.scenic_id,U.user_name,S.scenic_name,C.parent_comment_id FROM tab_scenic_comment C INNER JOIN tab_user U ON C.user_id=U.user_id INNER JOIN tab_scenic S ON C.scenic_id=S.scenic_id");
		ResultSet rs=db.query(sb.toString());
		try {
			while (rs.next()) {
				Integer  commentId  = rs.getInt("comment_id");
				String   commentContent = rs.getString("comment_content");
				String   commentDatetime = rs.getString("comment_datetime");
				Integer  userId = rs.getInt("user_id");
				Integer  scenicId = rs.getInt("scenic_id");
				Integer  parentCommentId = rs.getInt("parent_comment_id");
				String  userName = rs.getString("user_name");
				String  userPicture = rs.getString("user_picture");
				String  scenicName = rs.getString("scenic_name");
				
				
				ScenicComment  scenicComment = new  ScenicComment(commentId,commentContent,commentDatetime,userId,scenicId,parentCommentId,new User(null,userName,null,null,null,null,null,userPicture),new Scenic(null,scenicName,null,null,null));
				
				list.add(scenicComment);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int deleteById(int commentID) {
		return db.update("delete from tab_scenic_comment where comment_id = ?", commentID);
	}

	@Override
	public int insert(ScenicComment scenicComment) {
		String sql="insert into tab_scenic_comment(comment_id,comment_content,comment_datetime,user_id,scenic_id,parent_comment_id) values(?,?,?,?,?,?)";
		int i=db.update(sql, scenicComment.getCommentId(),scenicComment.getCommentContent(),scenicComment.getCommentDatetime(),scenicComment.getUserId(),scenicComment.getScenicId(),scenicComment.getParentCommentId());
		
		return i;
	}

	@Override
	public PageInfo<ScenicComment> findByPage(int pageNum, int pageSize) {
		// 1.获取分页结果的数据
		List<ScenicComment>  data= new ArrayList<ScenicComment>();
		StringBuffer sb=new StringBuffer();
		sb.append("select")
			.append(" C.comment_id,")
			.append(" C.comment_content,")
			.append(" C.comment_datetime,")
			.append(" C.user_id,")
			.append(" C.scenic_id,")
			.append(" C.parent_comment_id,")
			.append(" U.user_name,")
			.append(" S.scenic_name")
			.append(" from tab_scenic_comment C")
			.append(" inner join tab_user U")
			.append("  on C.user_id=U.user_id")
			.append(" inner join tab_scenic S")
			.append("  on C.scenic_id=S.scenic_id")
			.append(" limit ?,?");
		
		ResultSet rs  = db.query(sb.toString() ,(pageNum-1)*pageSize, pageSize);
		// 遍历结果集
		try {
			while (rs.next()) {
				Integer  commentId  = rs.getInt("comment_id");
				String   commentContent = rs.getString("comment_content");
				String   commentDatetime = rs.getString("comment_datetime");
				Integer  userId = rs.getInt("user_id");
				Integer scenicId=rs.getInt("scenic_id");
				Integer  parentCommentId = rs.getInt("parent_comment_id");
				String  userName = rs.getString("user_name");
				String  scenicName = rs.getString("scenic_name");
				
				ScenicComment  scenicComment = new  ScenicComment(commentId,commentContent,commentDatetime,userId,scenicId,parentCommentId,new User(null,userName,null,null,null,null,null,null),new Scenic(null,scenicName,null,null,null));
				// 添加到集合中
				data.add(scenicComment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// 2.获取总记录数(未分页之前)
		int total = 0;
		sb = new StringBuffer();
		sb.append("select")
		  .append(" count(*)")
		  .append(" from tab_scenic_comment ");
		
		rs  = db.query(sb.toString());
		// 遍历结果集
		try {
			while (rs.next()) {
				total  = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// 构建分页对象
		PageInfo<ScenicComment> pageInfo = new PageInfo<ScenicComment>(data, pageNum, pageSize, total);
		
		return pageInfo;
	}

}
